/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package modele;
import java.sql.*;
import java.util.*;
/**
 *
 * @author mbamba
 */
public class DAO {
 



    public static Collection<Dep>getLesDeps() {
        Collection<Dep>lesDeps=new TreeSet<Dep>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery( "select distinct departement from medecin");
            

//            Collection <Med> m;
//            int i = 0;
           while ( rs.next() ) {
                String mdepartement = rs.getString(1);
//                m = new TreeSet <Med> ();
//                Statement reqP = con.createStatement();
//                ResultSet rsP = reqP.executeQuery( 
//                                "select nom, prenom,adresse,tel,specialitecomplementaire,id " +
//                                "from medecin " +
//                                "where departement = '" + mdepartement + "'");
//                while ( rsP.next() ) {
//                    m.add(new Med(rsP.getString(1),rsP.getString(2),rsP.getString(3),rsP.getString(4),rsP.getString(5),rsP.getString(6)));
//                    
//                }
//                rsP.close();
//              
                lesDeps.add(new Dep(mdepartement));
//                i++;
            }
            rs.close();
        
        } catch(SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesDeps;
    }
 


 public static Collection<Spe>getLesSpes() {
        Collection<Spe>lesSpes=new TreeSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery( "select distinct specialitecomplementaire from medecin"
                    + " where specialitecomplementaire is not null");
            
            //Parcours
            String mspecialite;
//            Collection <Med> m;
//            int i = 0;
           while ( rs.next() ) {
               mspecialite = rs.getString(1);
//                m = new TreeSet <Med> ();
//                Statement reqP = con.createStatement();
//                ResultSet rsP = reqP.executeQuery( 
//                                "select nom, prenom,adresse,tel,specialitecomplementaire,id " +
//                                "from medecin " +
//                                "where departement = '" + mdepartement + "'");
//                while ( rsP.next() ) {
//                    m.add(new Med(rsP.getString(1),rsP.getString(2),rsP.getString(3),rsP.getString(4),rsP.getString(5),rsP.getString(6)));
//                    
//                }
//                rsP.close();
//              
                lesSpes.add(new Spe(mspecialite));
//                i++;
           }
            rs.close();
        
        } catch(SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesSpes;
    }
 
  public static Collection<Med>getLesMeds() {
        Collection<Med>lesMeds=new TreeSet<Med>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery( "select nom, prenom,adresse,tel,id,specialitecomplementaire,departement from medecin  ");
                               
                while ( rs.next() ) {
                   lesMeds.add(new Med(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7)));     
               }

            rs.close();
        
        } catch(SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesMeds;
    }
 
}
